﻿<# CMSUpdater.ps1

   Developed by: Mohit K. Gupta
   Avaliable at: http://CMSUpdater.codeplex.com
                 http://sqlcan.wordpress.com

   Last Updated: August 13, 2014

  Prerequisites: SQL Server 2012
                 Central Management Server
                 SCOM 2012 OperationsManager Database Access
                 Login that can read/write CMS Management Tables in MSDB
                 Login that can read SCOM database.
                 Create two groups in CMS for adding and moving servers.

         Inputs: GROUP_ID_NEW_DISCOVERY - REQUIRED
                 - Group ID in CMS.  Any new SQL Instances discovered in SCOM
                   will be added to this group.  

                 GROUP_ID_MISSING_SERVERS - REQUIRED
                 - Group ID in CMS.  Any SQL instance found to be missing in SCOM
                   but are registered in CMS are moved to this folder.

                 CMS_SERVER_NAME - REQUIRED

                 SCOM_SERVER_NAME - REQUIRED

                 CMS_DATABASE_NAME - OPTIONAL
                 - Defaults to 'msdb'.

                 SCOM_DATABASE_NAME - OPTIONAL
                 - Defaults to 'OperationsManager'.

                 SCOM_GROUPS_NOT_TO_MOVE - OPTIONAL
                 - Defaults to -1; there might be servers registered in CMS that SCOM 
                   cannot pick up, because these servers are in different domain for example.
                   Or they might be SQL Server 6.5, 7.0 or 2000; therefore they should not
                   be moved to the GROUP_ID_MISSING_SERVERS.

    Description: Microsoft relased Central Management Server (CMS) with SQL Server 2008.
                 However there is no easy way to keep the list of servers registered with in
                 CMS updated automatically.  Maybe new instance got installed or an instance
                 that was registered with CMS has been decomissioned?  There is no easy way
                 to track it.  However if your organization is using SCOM; then there is way.

                 SCOM has self discovery component for SQL Server 2005+.  Therefore we can 
                 mine that information for CMS and update CMS based on instances registered
                 with in SCOM.

       Comments: I have tested this script against SQL Server 2012 Central Management Server,
                 although not alot has changed between SQL Server 2008 CMS and SQL Server 2012
                 Central Management Servers; please verify script works for you before 
                 implementing it against your product server.  I only had access to SCOM 2012
                 when generating query for SCOM.  Please verify it is valid for your
                 enviornment.

- COPYRIGHT NOTICE -

Microsoft Public License (Ms-PL)

This license governs use of the accompanying software. If you use the software,
you accept this license. If you do not accept the license, do not use the software.

1. Definitions

The terms "reproduce," "reproduction," "derivative works," and "distribution" have
the same meaning here as under U.S. copyright law.

A "contribution" is the original software, or any additions or changes to the software.

A "contributor" is any person that distributes its contribution under this license.

"Licensed patents" are a contributor's patent claims that read directly on its contribution.

2. Grant of Rights

(A) Copyright Grant- Subject to the terms of this license, including the license conditions
    and limitations in section 3, each contributor grants you a non-exclusive, worldwide,
    royalty-free copyright license to reproduce its contribution, prepare derivative works
    of its contribution, and distribute its contribution or any derivative works that you
    create.

(B) Patent Grant- Subject to the terms of this license, including the license conditions
    and limitations in section 3, each contributor grants you a non-exclusive, worldwide,
    royalty-free license under its licensed patents to make, have made, use, sell, offer
    for sale, import, and/or otherwise dispose of its contribution in the software or
    derivative works of the contribution in the software.

3. Conditions and Limitations

(A) No Trademark License- This license does not grant you rights to use any contributors'
    name, logo, or trademarks.

(B) If you bring a patent claim against any contributor over patents that you claim are
    infringed by the software, your patent license from such contributor to the software
    ends automatically.

(C) If you distribute any portion of the software, you must retain all copyright, patent,
    trademark, and attribution notices that are present in the software.

(D) If you distribute any portion of the software in source code form, you may do so only
    under this license by including a complete copy of this license with your distribution.
    If you distribute any portion of the software in compiled or object code form, you may
    only do so under a license that complies with this license.

(E) The software is licensed "as-is." You bear the risk of using it. The contributors give
    no express warranties, guarantees or conditions. You may have additional consumer rights
    under your local laws which this license cannot change. To the extent permitted under
    your local laws, the contributors exclude the implied warranties of merchantability,
    fitness for a particular purpose and non-infringement.

- COPYRIGHT NOTICE -

Change History
--------------

Version    Date        Comment
V1.00      2014/08/13  Initial Development and Release
#>

param( 
[Parameter(Position=0, Mandatory=$true)] [int]$GROUP_ID_NEW_DISCOVERY = -1, 
[Parameter(Position=1, Mandatory=$true)] [int]$GROUP_ID_MISSING_SERVERS = -1, 
[Parameter(Position=2, Mandatory=$true)] [string]$CMS_SERVER_NAME = 'NOTCONFIGURED', 
[Parameter(Position=3, Mandatory=$true)] [string]$SCOM_SERVER_NAME = 'NOTCONFIGURED', 
[Parameter(Position=4, Mandatory=$false)] [string]$CMS_DATABASE_NAME = 'msdb', 
[Parameter(Position=5, Mandatory=$false)] [string]$SCOM_DATABASE_NAME = 'OperationsManager', 
[Parameter(Position=6, Mandatory=$false)] [string]$SCOM_GROUPS_NOT_TO_MOVE = '-1'
) 

# If the PowerShell script is being scheduled as part of Windows 
Import-Module SQLPS -DisableNameChecking

# TSQL Code -- Both statements below are not complete; they get completed in code later
$TSQL_InsertGroup = "INSERT INTO msdb.dbo.sysmanagement_shared_registered_servers (server_group_id,name,server_name,description, server_type)"
$TSQL_UpdateGroup = "UPDATE msdb.dbo.sysmanagement_shared_registered_servers"

# TSQL Code -- Complete Code - Get list of Servers in SCOM
$TSQL_GetSCOMServerList = "
   SELECT  DISTINCT
           LOWER(CASE WHEN (CHARINDEX('.',PATH) > 0) AND name <> 'mssqlserver' THEN
              SUBSTRING(PATH,1,CHARINDEX('.',PATH)-1) + '\' + Name
		   WHEN (CHARINDEX('.',PATH) > 0) AND name = 'mssqlserver' THEN
		      SUBSTRING(PATH,1,CHARINDEX('.',PATH)-1)
		   ELSE
		      PATH + '\' + Name
		   END) AS ServerNameInstanceName
         , LOWER(CASE WHEN Name <> 'mssqlserver' THEN
              PATH + '\' + Name
           ELSE
              PATH
           END) AS ServerNameFQDNInstanceName
    FROM ManagedEntityGenericView
   WHERE FullName Like 'Microsoft.SQLServer.DBEngine%'
     AND IsAvailable = 1 -- Signifies if the SCOM agent is not active.
ORDER BY ServerNameInstanceName"

# TSQL Code -- Complete Code - Get list of Groups and Servers registered in CMS
$TSQL_GetCMSServerList = "
WITH Groups (GroupID, GroupName)
AS
(
	SELECT server_group_id, '[' + CAST(name AS VARCHAR(1000))
	  FROM msdb.dbo.sysmanagement_shared_server_groups
	 WHERE parent_id IS NULL

	UNION ALL

	SELECT server_group_id, CAST(GroupName + '\' + CAST(name AS VARCHAR(100)) AS VARCHAR(1001))
	  FROM msdb.dbo.sysmanagement_shared_server_groups SSG
	  JOIN Groups G
	    ON SSG.parent_id = G.GroupID
)

  SELECT   GroupID
         , GroupName + ']' AS GroupName
         , SRS.server_id
         , LOWER(CASE WHEN CHARINDEX('.',server_name) > 0 AND CHARINDEX('\',SRS.server_name) = 0 THEN
              SUBSTRING(server_name,1,CHARINDEX('.',server_name)-1)
           WHEN CHARINDEX('.',server_name) > 0 AND CHARINDEX('\',SRS.server_name) > 0 THEN
              SUBSTRING(server_name,1,CHARINDEX('.',server_name)-1) + '\' + SUBSTRING(server_name,CHARINDEX('\',server_name)+1,LEN(server_name))
		   ELSE
		      SRS.server_name
           END) AS ServerNameInstanceName
	     , LOWER(SRS.server_name) AS ServerNameFQDNInstanceName
    FROM Groups G
    JOIN msdb.dbo.sysmanagement_shared_registered_servers SRS
      ON SRS.server_group_id = G.GroupID
   WHERE G.GroupID NOT IN ($GROUP_ID_MISSING_SERVERS)
ORDER BY ServerNameInstanceName"

try
{
    $SQLServersInSCOM = Invoke-SQLCMD -ServerInstance $SCOM_SERVER_NAME -Database $SCOM_DATABASE_NAME -Query $TSQL_GetSCOMServerList
}
catch
{
    $ErrorMessage = $_.Exception.Message
    $FailedItem = $_.Exception.ItemName
    Write-Error "Failed to get list of Servers from SCOM $FailedItem. The error message was $ErrorMessage"
    Break
}

try
{
    $SQLServersInCMS = Invoke-SQLCMD -ServerInstance $CMS_SERVER_NAME -Database $CMS_DATABASE_NAME -Query $TSQL_GetCMSServerList
}
catch
{
    $ErrorMessage = $_.Exception.Message
    $FailedItem = $_.Exception.ItemName
    Write-Error "Failed to get list of Servers from SCOM $FailedItem. The error message was $ErrorMessage"
    Break
}

$NewServers = 0
$ServersRemoved = 0

# Check #1: Any servers in SCOM that do not exist in CMS
ForEach ($SQLServerInSCOM in $SQLServersInSCOM)
{
    $ServerExists = $false

    ForEach ($SQLServerInCMS in $SQLServersInCMS)
    {
        if ($($SQLServerInSCOM.ServerNameInstanceName) -eq $($SQLServerInCMS.ServerNameInstanceName))
        {
            $ServerExists = $true
            break;
        }
    }

    if (!($ServerExists))
    {
        $NewServers++;
        $TSQL_Execution = "$TSQL_InsertGroup VALUES ($GROUP_ID_NEW_DISCOVERY,'$($SQLServerInSCOM.ServerNameFQDNInstanceName)','$($SQLServerInSCOM.ServerNameFQDNInstanceName)','',0)"
        Invoke-SQLCMD -ServerInstance $CMS_SERVER_NAME -Database $CMS_DATABASE_NAME -Query $TSQL_Execution
    }

}

# Check #2: Any servers in CMD that do not exist in SCOM
ForEach ($SQLServerInCMS in $SQLServersInCMS)
{
    $ServerExists = $false

    ForEach ($SQLServerInSCOM in $SQLServersInSCOM)
    {
        if ($($SQLServerInSCOM.ServerNameInstanceName) -eq $($SQLServerInCMS.ServerNameInstanceName))
        {
            $ServerExists = $true
            break;
        }
    }

    if (!($ServerExists))
    {
        if ($SCOM_GROUPS_NOT_TO_MOVE -notlike "*$($SQLServerInCMS.GroupID)*")
        { #Only move server if it is not in the DO NOT MOVE GROUPS.
            $ServersRemoved++;
            $TSQL_Execution = "$TSQL_UpdateGroup SET server_group_id = $GROUP_ID_MISSING_SERVERS, description = 'Moved from $($SQLServerInCMS.GroupName)' WHERE server_id = $($SQLServerInCMS.Server_ID)"
            Invoke-SQLCMD -ServerInstance $CMS_SERVER_NAME -Database $CMS_DATABASE_NAME -Query $TSQL_Execution
        }
    }
}

Write-output "Servers Added: $NewServers"
Write-output "Servers Removed (Moved): $ServersRemoved" 